--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库，名称为Student
use master;
go
-- 检测原库是否存在，如果存在则删除
if exists (select * from sys.databases where name = 'Student')
	drop database Student;
go
-- 创建一个库
create database Student;
go
-- 使用这个库
use Student;
go


--------------------
-- 建表部分
--------------------
-- 创建班级表，存储班级信息，其中字段包含：班级id、班级名称
create table Class(
	ClassId int not null identity(1,1),
	ClassName nvarchar(50) not null
);
go

-- 创建学生表，存储学生信息，其中字段保护：学生id、姓名、性别、生日、家庭住址，所属班级id
create table Student (
	StudentId int not null identity(1, 1),
	StudentName nvarchar(50),
	StudentSex tinyint not null,
	StudentBirth date,
	StudentAddress nvarchar(255) not null
);
go

-- 创建课程表，存储课程信息，其中字段包含：课程id、课程名称、课程学分
create table Course(
	CourseId int identity(1,1),
	CourseName nvarchar(50),
	CourseCredit int
);
go

-- 创建班级课程表，存储班级课程信息，其中字段包含：自增id、班级id、课程id
create table ClassCourse(
	ClassCourseId int identity(1,1),
	ClassId int,
	CourseId int
);
go

-- 创建分数表，存储学生每个课程分数信息，其中字段包含：分数id、学生id、课程id、分数
create table Score(
	ScoreId int identity(1,1),
	StudentId int,
	CourseId int,
	Score int
);
go


-- 学生表建好了，细想一下少了一个所属班级的字段
-- 给学生表 Student 增加一个所属班级id字段 
alter table Student add ClassId int not null;
go

----------------------------------------
-- 创建约束部分，使用alter进行修改
----------------------------------------
-- 班级表 ClassId 字段需要设置为主键（主键约束）
alter table Class add constraint PK_Class_ClassId primary key (ClassId);
-- 学生表 StudentId 字段需要设置为主键（主键约束）
alter table Student add constraint PK_Student_StudentId primary key (StudentId);
-- 课程表 CourseId 字段需要设置为主键（主键约束）
alter table Course add constraint PK_Course_CourseId primary key (CourseId);
-- 班级课程表 ClassCourseId 字段需要设置为主键（主键约束）
alter table ClassCourse add constraint PK_ClassCourse_ClassCourseId primary key (ClassCourseId);
-- 分数表 ScoreId 字段需要设置为主键（主键约束）
alter table Score add constraint PK_Score_ScoreId primary key (ScoreId);

-- 学生表 StudentName 不允许为空（非空约束）
alter table Student alter column StudentName nvarchar(50) not null;

-- 班级表 ClassName 需要唯一（唯一约束）
alter table Class add constraint UQ_Class_ClassName unique(ClassName);
-- 课程表 CourseName 需要唯一（唯一约束）
alter table Course add constraint UQ_Course_CourseName unique(CourseName);

-- 学生表 ClassId 增加默认值为0（默认值约束）
alter table Student add constraint DF_Student_ClassId default(0) for ClassId;

-- 学生表 StudentSex 只能为1或者2（Check约束）
alter table Student add constraint CK_Student_StudentSex check(StudentSex=1 or StudentSex=2);
-- 分数表 Score 字段只能大于等于0（check约束）
alter table Score add constraint CK_Score_Score check(Score>=0);
-- 课程表 CourseCredit 字段只能大于0（check约束）
alter table Course add constraint CK_Course_CourseCredit check(CourseCredit>=0);

-- 班级课程表ClassId 对应是 班级表ClassId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_ClassId foreign key (ClassId) references Class(ClassId);
-- 班级课程表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_CourseId foreign key (CourseId) references Course(CourseId);
-- 分数表StudentId 对应是 学生表StudentId 的外键 （外键约束）
alter table Score add constraint FK_Score_StudentId foreign key (StudentId) references Student(StudentId);
-- 分数表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table Score add constraint FK_Score_CourseId foreign key (CourseId) references Course(CourseId);


--------------------
-- 插入数据部分
--------------------
-- 学校开设了3个班级：软件一班、软件二班、计算机应用技术班。请插入班级表相关数据
insert into Class (ClassName) values ('软件一班');
insert into Class (ClassName) values ('软件二班');
insert into Class (ClassName) values ('计算机应用技术班');

-- 软件一班有3个同学，姓名、性别、生日、家庭住址  分别是：
--	刘正、男、2000-01-01、广西省桂林市七星区空明西路10号鸾东小区
--	黄贵、男、2001-03-20、江西省南昌市青山湖区艾溪湖南路南150米广阳小区
--	陈美、女、2000-07-08、福建省龙岩市新罗区曹溪街道万达小区
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('刘正',1,'2000-01-01','广西省桂林市七星区空明西路10号鸾东小区', 1);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('黄贵',1,'2001-03-20','江西省南昌市青山湖区艾溪湖南路南150米广阳小区', 1);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('陈美',2,'2000-07-08','福建省龙岩市新罗区曹溪街道万达小区', 1);


-- 软件二班有2个同学，姓名、性别、生日、家庭住址  分别是：
--	江文、男、2000-08-10、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
--	钟琪、女、2001-03-21、湖南省长沙市雨花区红花坡社区
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('江文',1,'2000-08-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光', 2);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('钟琪',2,'2001-03-21','湖南省长沙市雨花区红花坡社区', 2);

-- 计算机应用技术班有4个同学，姓名、性别、生日、家庭住址  分别是：
--	曾小林、男、1999-12-10、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
--	欧阳天天、女、2000-04-05、湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府
--	徐长卿、男、2001-01-30、江苏省苏州市苏州工业园区独墅湖社区
--	李逍遥、男、1999-11-11、广东省广州市白云区金沙洲岛御洲三街恒大绿洲
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('曾小林',1,'1999-12-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('欧阳天天',2,'2000-04-05','湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('徐长卿',1,'2001-01-30','江苏省苏州市苏州工业园区独墅湖社区', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('李逍遥',1,NULL,'广东省广州市白云区金沙洲岛御洲三街恒大绿洲', 3);

-- 软件一班开设课程，课程名称和学分分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计.net基础、6
insert into Course(CourseName, CourseCredit) values ('数据库高级应用', 3);
insert into Course(CourseName, CourseCredit) values ('javascript编程基础', 3);
insert into Course(CourseName, CourseCredit) values ('web前端程序设计基础', 4);
insert into Course(CourseName, CourseCredit) values ('动态网页设计.net基础', 6);

insert into ClassCourse (ClassId, CourseId) values (1, 1);
insert into ClassCourse (ClassId, CourseId) values (1, 2);
insert into ClassCourse (ClassId, CourseId) values (1, 3);
insert into ClassCourse (ClassId, CourseId) values (1, 4);

-- 软件二班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计.net基础、6

insert into ClassCourse (ClassId, CourseId) values (2, 1);
insert into ClassCourse (ClassId, CourseId) values (2, 2);
insert into ClassCourse (ClassId, CourseId) values (2, 3);
insert into ClassCourse (ClassId, CourseId) values (2, 4);

-- 计算机应用技术班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计php基础、6
insert into Course(CourseName, CourseCredit) values ('动态网页设计php基础', 6);

insert into ClassCourse (ClassId, CourseId) values (3, 1);
insert into ClassCourse (ClassId, CourseId) values (3, 2);
insert into ClassCourse (ClassId, CourseId) values (3, 3);
insert into ClassCourse (ClassId, CourseId) values (3, 5);


-- 考试完成后，各学生各课程得分：
--	刘正、数据库高级应用、80
--	刘正、javascript编程基础、78
--	刘正、web前端程序设计基础、65
--	刘正、动态网页设计.net基础、90
--	黄贵、数据库高级应用、60
--	黄贵、javascript编程基础、77
--	黄贵、web前端程序设计基础、68
--	黄贵、动态网页设计.net基础、88
--	陈美、数据库高级应用、88
--	陈美、javascript编程基础、45
--	陈美、web前端程序设计基础、66
--	陈美、动态网页设计.net基础、75
insert into Score (StudentId, CourseId, Score) values (1, 1, 80);
insert into Score (StudentId, CourseId, Score) values (1, 2, 78);
insert into Score (StudentId, CourseId, Score) values (1, 3, 65);
insert into Score (StudentId, CourseId, Score) values (1, 4, 90);

insert into Score (StudentId, CourseId, Score) values (2, 1, 60);
insert into Score (StudentId, CourseId, Score) values (2, 2, 77);
insert into Score (StudentId, CourseId, Score) values (2, 3, 68);
insert into Score (StudentId, CourseId, Score) values (2, 4, 88);

insert into Score (StudentId, CourseId, Score) values (3, 1, 88);
insert into Score (StudentId, CourseId, Score) values (3, 2, 45);
insert into Score (StudentId, CourseId, Score) values (3, 3, 66);
insert into Score (StudentId, CourseId, Score) values (3, 4, 75);
go

--	江文、数据库高级应用、56
--	江文、javascript编程基础、80
--	江文、web前端程序设计基础、75
--	江文、动态网页设计.net基础、66
--	钟琪、数据库高级应用、88
--	钟琪、javascript编程基础、79
--	钟琪、web前端程序设计基础、72
--	钟琪、动态网页设计.net基础、85

insert into Score (StudentId, CourseId, Score) values (4, 1, 56);
insert into Score (StudentId, CourseId, Score) values (4, 2, 80);
insert into Score (StudentId, CourseId, Score) values (4, 3, 75);
insert into Score (StudentId, CourseId, Score) values (4, 4, 66);

insert into Score (StudentId, CourseId, Score) values (5, 1, 88);
insert into Score (StudentId, CourseId, Score) values (5, 2, 79);
insert into Score (StudentId, CourseId, Score) values (5, 3, 72);
insert into Score (StudentId, CourseId, Score) values (5, 4, 85);


--	曾小林、数据库高级应用、68
--	曾小林、javascript编程基础、88
--	曾小林、web前端程序设计基础、73
--	曾小林、动态网页设计php基础、63
--	欧阳天天、数据库高级应用、84
--	欧阳天天、javascript编程基础、90
--	欧阳天天、web前端程序设计基础、92
--	欧阳天天、动态网页设计php基础、78
--	徐长卿、数据库高级应用、58
--	徐长卿、javascript编程基础、59
--	徐长卿、web前端程序设计基础、65
--	徐长卿、动态网页设计php基础、75
--	李逍遥、数据库高级应用、48
--	李逍遥、javascript编程基础、67
--	李逍遥、web前端程序设计基础、71
--	李逍遥、动态网页设计.net基础、56
insert into Score (StudentId, CourseId, Score) values (6, 1, 68);
insert into Score (StudentId, CourseId, Score) values (6, 2, 88);
insert into Score (StudentId, CourseId, Score) values (6, 3, 73);
insert into Score (StudentId, CourseId, Score) values (6, 5, 63);

insert into Score (StudentId, CourseId, Score) values (7, 1, 84);
insert into Score (StudentId, CourseId, Score) values (7, 2, 90);
insert into Score (StudentId, CourseId, Score) values (7, 3, 92);
insert into Score (StudentId, CourseId, Score) values (7, 5, 78);

insert into Score (StudentId, CourseId, Score) values (8, 1, 58);
insert into Score (StudentId, CourseId, Score) values (8, 2, 59);
insert into Score (StudentId, CourseId, Score) values (8, 3, 65);
insert into Score (StudentId, CourseId, Score) values (8, 5, 75);

insert into Score (StudentId, CourseId, Score) values (9, 1, 48);
insert into Score (StudentId, CourseId, Score) values (9, 2, 67);
insert into Score (StudentId, CourseId, Score) values (9, 3, 71);
insert into Score (StudentId, CourseId, Score) values (9, 5, 56);
insert into Score (StudentId, CourseId, Score) values (9, 5, 56);
go

--------------------
-- 删除数据部分
--------------------
-- 分数表这边 分数表 最后一条插入重复了，请帮忙删除这条数据
select * from Score order by ScoreId desc;
delete from Score where ScoreId = 37;

--------------------
-- 修改数据部分
--------------------
-- 计算机应用技术班 的 欧阳天天 生日写错了，正确的生日应该是：2000-04-06，请用sql进行修改。（update）
update Student set StudentBirth='2000-04-06' where StudentId = 7;
go
-- 计算机应用技术班 的 徐长卿 的 javascript编程基础 分数填错，正确的分数应该是：61，请用sql进行修改。（update）
update Score set Score = 61 where StudentId = 8 and CourseId = 2;
go


select * from Class;
select * from Student;
select * from Course;
select * from ClassCourse;
select * from Score;

--查询软件一班所有学生信息 显示学生id 姓名 性别 生日 住址
select * from Student where ClassId=1;

--查询所有女生 显示学生id 姓名 性别 生日 住址
select * from Student where StudentSex=2;

--查询2000年出生的所有学生 显示学生id 姓名 性别 生日 住址
select * from Student where StudentBirth between '2000-01-01' and '2000-12-31';



--创建学生信息表stuinfo
create table stuinfo(
	stuNO nvarchar(50) primary key,
	stuName nvarchar(50) not null,
	stuAge int not null,
	stuAddress nvarchar(225) not null,
	stuSeat int not null,
	stuSex int not null
);
insert into stuinfo(stuNO,stuName,stuAge,stuAddress,stuSeat,stuSex)
	values('s2501','张秋利',20,'美国硅谷',1,1)
	,('s2502','李斯文',18,'湖北武汉',2,0)
	,('s2503','马文才',22,'湖南长沙',3,1)
	,('s2504','欧阳俊雄',21,'湖北武汉',4,0)
	,('s2505','梅超风',20,'湖北武汉',5,1)
	,('s2506','陈旋风',19,'美国硅谷',6,1)
	,('s2507','陈风',20,'美国硅谷',7,0)

--查询学生信息表（stuinfo）中所有列信息，给每列取上中文名称
select 学号=stuNO,姓名=stuName,年龄=stuAge,家庭地址=stuAddress,座号=stuSeat,性别=stuSex from stuinfo
--查询学生信息表（stuinfo）中的姓名，年龄和地址三列的信息
select stuName 姓名,stuAge 年龄,stuAddress 地址 from stuinfo
select * from stuinfo

--创建学生分数表stuexam
create table stuexam(
	examNO int  identity(1,1),
	stuNO nvarchar(50),
	writtenExam int not null,
	labExam int not null
);
insert into stuexam(stuNO,writtenExam,labExam)
	values('s2501',50,70)
	,('s2502',60,65)
	,('s2503',86,85)
	,('s2504',40,80)
	,('s2505',70,90)
	,('s2506',85,90);
alter table stuexam add constraint FK_stuexam_StuNO foreign key(stuNO) references stuinfo(stuNO);
--查询学生分数表（stuexam）中的学号，笔试和机试三列的信息，并为这三列取中文名字
--注意：要用三种方法
select stuNO 学号, writtenExam 笔试,labExam 机试 from stuexam;
select stuNO as 学号, writtenExam as 笔试,labExam as 机试 from stuexam;
select 学号=stuNO, 笔试=writtenExam,机试=labExam from stuexam;
--查询学生信息表（stuInfo）中的学号，姓名，地址，以及将：姓名+@+地址 组成新列 “邮箱”
select stuNo 学号, stuName 学生姓名, stuAddress 家庭地址, stuName+'@'+stuAddress 邮箱 from stuinfo;
--5.查询学生分数表（stuexam）中的学生的学号，笔试，机试以及总分这四列的信息
select stuNO,writtenExam,labExam from stuexam;
select * from stuexam;

--查询姓是欧阳的学生
select * from Student where StudentName like '欧阳%' 
--查询地址是桂林市的学生
select *from Student where StudentAddress like '%桂林市%'
--查询姓李的学生，并且是三个字的
select * from Student where StudentName like '李__'
--查询 软件一班 有几个学生
select count(*) 软件一班总人数 from Student where ClassId=1
--查询 软件一班 有几门课程
select count(*) 软件一班课程数 from ClassCourse where ClassId=1
--查询 刘正(学生编号为1) 的总分
select sum(Score) 刘正的总分 from Score where StudentId=1
--查询所有学生 数据库高级应用 的平均分
select avg(Score) 数据库平均分 from Score where CourseId=1
--查询 所有学生 的总分
select StudentId, sum(Score) as 所有学生总分 from Score group by StudentId;
--查询 所有学生 的平均分
select Studentid, avg(Score) as 所有学生平均分 from score group by StudentId;
--查询 所有学生 的平均分，并且按照平均分从高到低排列
select StudentId, avg(Score) as 平均分降序 from Score group by StudentId order by 平均分降序 desc
--查询 所有学生中 平均分 大于80分的学生。（聚合查询 + 分组 + having）
select Studentid, avg(Score) as 平均分大于80分 from Score group by StudentId having avg(Score)>80;
--查询学生信息表（stuInfo）中前3行记录 
select top 3 * from stuinfo
--查询学生信息表（stuInfo）中前4个学生的姓名和座位号
select top 4 stuName,stuSeat from stuinfo
--查询学生信息表（stuInfo）中一半学生的信息
select top 50 percent * from stuinfo
--将地址是湖北武汉，年龄是20的学生的所有信息查询出来
select * from stuinfo where stuAddress like '%湖北武汉%' and stuAge=20
--将机试成绩在60-80之间的信息查询出来，并按照机试成绩降序排列（用两种方法实现）
select * from stuexam where labExam between 60 and 80 order by labExam desc;
select *from stuexam where labExam>=60 and labExam <=80 order by labExam desc;
--查询来自湖北武汉或者湖南长沙的学生的所有信息（用两种方法实现）
select * from stuinfo where stuAddress like '%湖北武汉%' or stuAddress like '%湖南长沙%'
select * from stuinfo where stuAddress in ('湖北武汉','湖南长沙')
--查询出笔试成绩不在70-90之间的信息,并按照笔试成绩升序排列（用两种方法实现）
select * from stuexam where writtenExam<70 or writtenExam>90 order by writtenExam asc;
select * from stuexam where writtenExam not between 70 and 90 order by writtenExam asc;
--查询年龄没有写的学生所有信息
select * from stuinfo where stuAge is null;
--查询年龄写了的学生所有信息
select * from stuinfo where stuAge is not null;
--查询姓张的学生信息
select * from stuinfo where stuName like '张%'
--查询学生地址中有‘湖’字的信息
select *from stuinfo where stuAddress like '%湖%'
--查询姓张但名为一个字的学生信息
select *from stuinfo where stuName like '张'
--查询姓名中第三个字为‘俊’的学生的信息，‘俊’后面有多少个字不限制
select * from stuinfo where stuName like '__俊%'
--按学生的年龄降序显示所有学生信息
select * from stuinfo order by stuAge desc;
--按学生的年龄降序和座位号升序来显示所有学生的信息
select * from stuinfo order by stuAge desc,stuSeat asc;
--显示笔试第一名的学生的考试号，学号，笔试成绩和机试成绩
select top 1 * from stuexam order by writtenExam desc;
--显示机试倒数第一名的学生的考试号，学号，笔试成绩和机试成绩
select top 1 * from stuexam order by labExam asc;